package cn.edu.nju.software.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatabaseHelper {

	public static final int TID_ADMIN = 0;
	public static final int TID_MEMBER = 1;
	public static final int TID_DISPLAY = 2;

	private static final String TNAME_ADMIN = "admin";
	private static final String TNAME_MEMBER = "member";
	private static final String TNAME_DISPLAY = "display";
	private static final String TNAME_HALL = "hall";
	private static final String TNAME_LEVEL = "member_level";

	/**
	 * Columns of table member.
	 */
	public static final String T_MEMBER_C_USERNAME = "username";
	public static final String T_MEMBER_C_PASSWORD = "password";
	public static final String T_MEMBER_C_LID = "level_id";
	public static final String T_MEMBER_C_DEADLINE = "deadline";
	public static final String T_MEMBER_C_STATUS = "status";
	public static final String T_MEMBER_C_BALANCE = "balance";
	public static final String T_MEMBER_C_GENDER = "gender";
	public static final String T_MEMBER_C_AGE = "age";

	/**
	 * Columns of table display.
	 */
	private static final String T_DISPLAY_C_ID = "display_id";
	private static final String T_DISPLAY_C_HALL = "hall";
	private static final String T_DISPLAY_C_MOVIE = "movie_name";
	private static final String T_DISPLAY_C_TIME = "time";
	private static final String T_DISPLAY_C_CAPACITY = "capacity";
	private static final String T_DISPLAY_C_STATUS = "status";
	private static final String T_DISPLAY_C_SURPLUS = "surplus";
	private static final String T_DISPLAY_C_PRICE = "price";

	/**
	 * Columns of table level.
	 */
	private static final String T_LEVEL_C_ID = "level_id";
	private static final String T_LEVEL_C_DISCOUNT = "discount";

	/**
	 * Columns of table hall.
	 */
	private static final String T_HALL_C_HALLNAME = "hall_name";

	private static Connection con;

	public DatabaseHelper() {
		if (con == null) {
			try {
				con = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/cineplex"
								+ "?useUnicode=true&characterEncoding=UTF-8",
						"root", "123456");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * fetch all records from a table determined by the table id.
	 * 
	 * @param tableId
	 * @return a result set contains the information
	 */
	public ResultSet fetchAllRecord(int tableId) {
		ResultSet rs = null;
		String table = "";
		try {
			Statement stmt = con.createStatement();
			switch (tableId) {
			case TID_ADMIN:
				table = TNAME_ADMIN;
				break;
			case TID_MEMBER:
				table = TNAME_MEMBER;
				break;
			case TID_DISPLAY:
				table = TNAME_DISPLAY;
				break;
			}
			String query = "SELECT * FROM " + table;
			System.out.println("dbHelper: " + query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/*
	 * members
	 */

	public void registerMember(String username, String password, int levelId,
			String deadline, int status, int balance, String gender, String age) {
		try {
			Statement stmt = con.createStatement();
			String query = "INSERT INTO " + TNAME_MEMBER + "("
					+ T_MEMBER_C_USERNAME + ", " + T_MEMBER_C_PASSWORD + ", "
					+ T_MEMBER_C_LID + ", " + T_MEMBER_C_DEADLINE + ", "
					+ T_MEMBER_C_STATUS + ", " + T_MEMBER_C_BALANCE + ", "
					+ T_MEMBER_C_GENDER + ", " + T_MEMBER_C_AGE + ") "
					+ "VALUES(" + "'" + username + "', " + "'" + password
					+ "', " + levelId + ", '" + deadline + "', " + status
					+ ", " + balance + ", '" + gender + "', '" + age + "')";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void alterMemberStatus(String username, int levelId,
			String deadline, int status, int balance) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_MEMBER + " SET " + T_MEMBER_C_LID
					+ " = " + levelId + ", " + T_MEMBER_C_DEADLINE + " = '"
					+ deadline + "', " + T_MEMBER_C_STATUS + " = " + status
					+ ", " + T_MEMBER_C_BALANCE + " = " + balance + " WHERE "
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet fetchMemberInformation(String username) {
		ResultSet rs = null;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT * FROM " + TNAME_MEMBER + " WHERE "
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println("dbHelper: " + query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}

	public void updatePassword(String username, String password) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_MEMBER + " SET "
					+ T_MEMBER_C_PASSWORD + " = '" + password + "' WHERE "
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateInformation(String username, String gender, String age) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_MEMBER + " SET "
					+ T_MEMBER_C_GENDER + " = '" + gender + "', "
					+ T_MEMBER_C_AGE + " = '" + age + "'" + " WHERE "
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateBalance(String username, int balance) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_MEMBER + " SET "
					+ T_MEMBER_C_BALANCE + " = " + balance + " WHERE "
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int fetchBalance(String username) {
		int balance = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT " + T_MEMBER_C_BALANCE + " FROM "
					+ TNAME_MEMBER + " WHERE " + T_MEMBER_C_USERNAME + " = '"
					+ username + "'";
			System.out.println("dbHelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				balance = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return balance;
	}

	public int fetchDiscount(String username) {
		int discount = 100;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT " + T_LEVEL_C_DISCOUNT + " FROM "
					+ TNAME_LEVEL + ", " + TNAME_MEMBER + " WHERE "
					+ TNAME_LEVEL + "." + T_LEVEL_C_ID + " = " + TNAME_MEMBER
					+ "." + T_MEMBER_C_LID + " AND " + TNAME_MEMBER + "."
					+ T_MEMBER_C_USERNAME + " = '" + username + "'";
			System.out.println(query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				discount = rs.getInt(1);
				System.out.println(" discount: " + discount);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return discount;
	}

	public int fetchMemberCount(String column, String value) {
		int count = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT COUNT(" + column + ") FROM " + TNAME_MEMBER
					+ " WHERE " + column + " = '" + value + "'";
			System.out.println("dbHelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				count = rs.getInt(1);
				System.out.println(" value: " + count);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return count;
	}

	public int fetchMemberCount(String column, int value) {
		int count = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT COUNT(" + column + ") FROM " + TNAME_MEMBER
					+ " WHERE " + column + " = " + value;
			System.out.println("dbHelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				count = rs.getInt(1);
				System.out.println(" value: " + count);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return count;
	}

	/*
	 * displays
	 */
	public void addDisplay(String hall, String movie, String time,
			int capacity, int status, int surplus, int price) {
		try {
			Statement stmt = con.createStatement();
			String query = "INSERT INTO " + TNAME_DISPLAY + "("
					+ T_DISPLAY_C_HALL + ", " + T_DISPLAY_C_MOVIE + ", "
					+ T_DISPLAY_C_TIME + ", " + T_DISPLAY_C_CAPACITY + ", "
					+ T_DISPLAY_C_STATUS + ", " + T_DISPLAY_C_SURPLUS + ", "
					+ T_DISPLAY_C_PRICE + ") " + "VALUES(" + "'" + hall
					+ "', '" + movie + "', '" + time + "', " + capacity + ", "
					+ status + ", " + surplus + ", " + price + ")";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void alterDisplay(String hall, String movie, String time,
			int capacity, int status, int surplus, int price) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_DISPLAY + " SET "
					+ T_DISPLAY_C_MOVIE + " = '" + movie + "', "
					+ T_DISPLAY_C_TIME + " = '" + time + "', "
					+ T_DISPLAY_C_CAPACITY + " = " + capacity + ", "
					+ T_DISPLAY_C_STATUS + " = " + status + ", "
					+ T_DISPLAY_C_SURPLUS + " = " + surplus + ", "
					+ T_DISPLAY_C_PRICE + " = " + price + " WHERE "
					+ T_DISPLAY_C_HALL + " = '" + hall + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int fetchPrice(String hall, String movie) {
		int price = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT " + T_DISPLAY_C_PRICE + " FROM "
					+ TNAME_DISPLAY + " WHERE " + T_DISPLAY_C_HALL + " = '"
					+ hall + "' AND " + T_DISPLAY_C_MOVIE + " = '" + movie
					+ "'";
			System.out.println("dbHelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				price = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return price;
	}

	public int fetchSurplus(String hall, String movie) {
		int surplus = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT " + T_DISPLAY_C_SURPLUS + " FROM "
					+ TNAME_DISPLAY + " WHERE " + T_DISPLAY_C_HALL + " = '"
					+ hall + "' AND " + T_DISPLAY_C_MOVIE + " = '" + movie
					+ "'";
			System.out.println("dbHelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				surplus = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return surplus;
	}

	public int fetchDisplayId(String hall, String movie) {
		int id = 0;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT " + T_DISPLAY_C_ID + " FROM "
					+ TNAME_DISPLAY + " WHERE " + T_DISPLAY_C_HALL + " = '"
					+ hall + "' AND " + T_DISPLAY_C_MOVIE + " = '" + movie
					+ "'";
			System.out.println("dbhelper: " + query);
			ResultSet rs = stmt.executeQuery(query);
			if (rs != null) {
				rs.beforeFirst();
				rs.next();
				id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return id;
	}

	public void updateSurplus(String hall, String movie, int surplus) {
		try {
			Statement stmt = con.createStatement();
			String query = "UPDATE " + TNAME_DISPLAY + " SET "
					+ T_DISPLAY_C_SURPLUS + " = " + surplus + " WHERE "
					+ T_DISPLAY_C_HALL + " = '" + hall + "' AND "
					+ T_DISPLAY_C_MOVIE + " = '" + movie + "'";
			System.out.println("dbHelper: " + query);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet fetchAllMovies() {
		ResultSet rs = null;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT DISTINCT " + T_DISPLAY_C_MOVIE + " FROM "
					+ TNAME_DISPLAY;
			System.out.println("dbHelper: " + query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public ResultSet fetchMyMovies(ArrayList<Integer> store) {
		ResultSet rs = null;

		String condition = "";
		for (Integer id : store) {
			String tmp = T_DISPLAY_C_ID + " = " + id;
			condition += (condition.isEmpty()) ? tmp : " OR " + tmp;
		}
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT * FROM " + TNAME_DISPLAY + " WHERE "
					+ condition;
			System.out.println(query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}

	/*
	 * halls
	 */
	public ResultSet fetchAllHalls() {
		ResultSet rs = null;
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT DISTINCT " + T_HALL_C_HALLNAME + " FROM "
					+ TNAME_HALL;
			System.out.println("dbHelper: " + query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
}
